﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.Text;
using Common;
using Common.Database;

namespace BLL.SchemaDiscovery
{
    [DataContract]
    public class ColumnNameType
    {
        [DataMember]
        public string ColumnName { get; set; }
        [DataMember]
        public int DataType { get; set; }
        [DataMember]
        public int Size { get; set; }
    }

    
    public partial class SqlServerManager
    {
        public static List<string> ListDbServers()
        {
            List<string> dbServers=new List<string>();
            try
            {
                string[] dbHosts = SqlServerEnumUtil.GetAvailableServers();
                dbServers.AddRange(dbHosts);
            }
            catch
            {
                dbServers.Add("localhost");
                dbServers.Add("DLS7GP");
                dbServers.Add("(LOCAL)");
            }
            return dbServers;
        }

        public static List<string> ListDbs(string serverName)
        {
            string connStr = string.Format("Server={0};Database=Master;Integrated Security=sspi;", serverName);
            string[] dbs = DbUtil.GetDatabases(connStr);
            List<string> dbNames=new List<string>();
            dbNames.AddRange(dbs);
            return dbNames;
        }

        public static List<string> ListDbSchemas(string serverName, string dbName)
        {
            string connStr = string.Format("Server={0};Database={1};Integrated Security=SSPI", serverName, dbName);
            string[] schemas = DbUtil.GetSchemaNames(DataProviderType.MSSQL, connStr);
            List<string> schemaNames=new List<string>();
            schemaNames.AddRange(schemas);
            return schemaNames;
        }

        public static List<string> ListTables(string serverName, string dbName, string schemaName)
        {
            string connStr = string.Format("Server={0};Database={1};Integrated Security=SSPI", serverName, dbName);
            Dictionary<string,List<string>> schemaTbls = 
                DbUtil.GetTableNamesWithSchemas(DataProviderType.MSSQL, connStr);
            List<string> tblNames=new List<string>();
            tblNames.AddRange(schemaTbls[schemaName]);
            return tblNames;
        }

        public static List<string> ListTableColumnNames(string serverName, string dbName, string schemaName, string tblName)
        {
            string connStr = string.Format("Server={0};Database={1};Integrated Security=SSPI", serverName, dbName);
            string[] cols = DbUtil.GetTableColumnNames(DataProviderType.MSSQL, connStr, schemaName, tblName);
            List<string> colNames=new List<string>();
            colNames.AddRange(cols);
            return colNames;
        }

        public static List<string> ListTableColumnNamesUsingSQL(string serverName, string dbName, string sql)
        {
            string connStr = string.Format("Server={0};Database={1};Integrated Security=SSPI", serverName, dbName);
            TableColumn[] cols = DbUtil.GetTableColumns(connStr, sql);
            List<string> colNames = new List<string>();
            foreach (TableColumn col in cols)
            {
                colNames.Add(col.ColumnName);
            }
            return colNames;
        }

        public static List<ColumnNameType> ListTableColumns(string serverName, string dbName, string schemaName, string tblName)
        {
            string connStr = string.Format("Server={0};Database={1};Integrated Security=SSPI", serverName, dbName);
            TableColumn[] cols = DbUtil.GetTableColumns(DataProviderType.MSSQL, connStr, schemaName, tblName);
            List<ColumnNameType> colDbTypes = new List<ColumnNameType>();
            foreach(TableColumn col in cols)
            {
                SimpleDataType dbType = SimpleDataTypeUtil.ToSimpleDataType(col.DbType);
                ColumnNameType columnNameType=new ColumnNameType()
                                                  {
                                                      ColumnName=col.ColumnName,
                                                      DataType=(int)dbType,
                                                      Size=col.Size
                                                  };
                colDbTypes.Add(columnNameType);
            }
            return colDbTypes;
        }
    }
}
